SQL Server Extended Events: Creating Sessions, Events, Actions, Filters, and Targets

Comments 0

Share to social media

Share on X (Twitter) Share on LinkedIn Share on Mastodon Share on Facebook
SQL Server Extended Events (often shortened to XEvents) is the modern diagnostic and monitoring framework that replaces SQL Trace and SQL Server Profiler. It’s lower-overhead, more flexible, and the only one Microsoft actively develops since SQL Server 2008. Every Extended Events session is built from the same five components: events (what to capture – deadlocks, long queries, blocking, memory-grants, hundreds of others), actions (additional data to collect per event – session ID, client app name, stack), filters (predicates to limit what’s recorded so overhead stays manageable), targets (where the data goes – ring buffer in memory, event_file on disk, histogram, pair matching), and options (buffer memory, dispatch latency, event retention). This article walks through each building block in order, with the T-SQL syntax for creating and starting a session, and finishes with the XML-shred query pattern for reading the collected data back out of an event_file target. Use it as either a step-by-step introduction or a reference when you know which piece you need to build next. Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I’ll explain the building blocks of Extended Events data collection. While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging. This article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.

The basics of collecting and reading extended events data

The basic steps to use Extended Events are as follows:
  1. Create an Extended Events session
    1. Add events to the Extended Events session
    2. Add actions to the Extended Events session
    3. Add filters
    4. Add a target for event data
    5. Specify options
  2. Start the Extended Events session
  3. Periodically read data from the Extended Events session
Extended Events may be worked with via the SQL Server Management Studio GUI or using T-SQL. For this article, T-SQL will be used exclusively as it allows for code to be more easily shared and manipulated and is easier to copy and use than a sequence of images or videos. Once data is read from Extended Events, it needs to be parsed and placed into a more permanent location for analytics, monitoring, and/or posterity. The following is a brief review of creating and managing Extended Events sessions, followed by a more detailed process that automates collecting, extracting, and managing Extended Events processes and data. This is separated into distinct sections as each will become a modular script within the Extended Events solution crafted in this article.

Creating an Extended Events session

Creating a new Extended Events session via T-SQL involves deciding up-front how to configure and run it. Typically, filters, events, and options will be tweaked afterwards as the resulting data is reviewed and a better idea of how it should look is envisioned. The following is the most bare-bones way to create an Extended Events session: This script creates an event session, names it, and adds a single event. All other Extended Event options will use their default settings The session’s existence can be confirmed via sys.server_event_sessions: The results return a row with some additional details: Image showing details about the extended event session Note the value of 0 provided by startup_state, which indicates that the Extended Events session exists but is stopped. The view sys.dm_xe_sessions can also be used to check for the existence of active Extended Events sessions: This time, no results are returned: Image showing no active extended event sessions The lack of rows returned is another way to confirm that this session is not started. Creating an Extended Events session with no additional parameters is not a good idea. It is good practice to provide as much detail as possible about the target, events, actions, filters, and options.

Add events to the Extended Events session

An event is a response to a specific triggered occurrence in SQL Server. It could be a deadlock, a completed query, or a user login. All of the available events in SQL Server can be browsed via system views: This query returns some basic information about each Extended Events package: Information about each extended event package This query returns a list of all events. Note that the view dm_xe_objects also contains types, targets, actions, and other entities, as given by the column object_type. The results are extensive, but easy to filter: Image showing extended event packages At least one event is required for an Extended Events session, but multiple may be added like this: This script allows for any number of events to be attached to an Extended Events session. Keep in mind that data for each event is written to the Extended Event target, regardless of whether it is needed or not. Therefore, it is worthwhile to only include needed events as extraneous events will slow down Extended Events, waste storage space, and slow down event processing.

Add actions to the Extended Events session

Several data points are automatically collected for each event type. Actions allow additional elements to be retrieved along with their respective event, thereby customizing events with data that does not need to be retrieved by default for all use-cases. Actions are added onto events like this: For the two events in this example, 5 additional fields are added to those events as actions and will be included in the result set. Only add actions that are needed as they take up space and computing resources to collect.

Add filters to the Extended Events session

Filters allow results to be limited to those required for the monitoring/troubleshooting task at hand. They remove extraneous results and decrease the size of the extended event payload data, which will make consumption of that data faster and more efficient. Filters are added as WHERE clauses onto each event: These WHERE clauses filter out all events that originate from SQL Server Agent by filtering on client_app_name. There are likely multiple ways to filter out a given set of events for any given Extended Events session. Frequently used are filters against client_app_name, database_name, client_hostname, or username. These allow the source of events to be filtered up-front before data is written to a target.

Add a target to the extended events session

Event data needs to be written somewhere, and that somewhere needs to have the resources to accept whatever volume of data will originate from a given Extended Events Session. A complete list of all target types is available here: Targets for Extended Events in SQL Server – SQL Server | Microsoft Docs While that list is long, most users of Extended Events will use either the ring buffer or a file as the target for their event output. The rest are worth reviewing to gain knowledge and to understand what else is available but are typically used for less common tasks. The ring buffer stores events in memory and will automatically clear itself out as a memory cap is reached or if a maximum event count is reached. The ring buffer is great for testing Extended Events and developing new code, but is not ideal for production workloads, especially larger ones. A ring buffer target can be specified like this: Note that it is possible to specify multiple targets for an Extended Events session if there is a need to do so. The most common target for event data is a file, which is stored in an XML format with a *.XEL file extension, by default. A file target allows for more customization of its size and usage and can accommodate far more data than the ring buffer. In addition, if the database server is restarted for any reason, the data present in a file will persist, whereas ring buffer data is stored in memory and will no longer be available. A file target can be added using similar code: This command sets the target to a file on my local drive with a file size of 1000MB and up to 3 rollover files. Rollover files allow Extended Events files to be subdivided into smaller units, allowing for faster access and easier removal.

Specify options for an Extended Events session

The last step in configuring an Extended Events session is to set options that determine how events are captured, stored, and maintained. The following is an example of a few options that are added to the growing demonstration from above: The MAX_MEMORY setting determines the size of the memory buffer used to capture event data prior to writing it to the file target. The event retention mode allows for some data loss to occur in the event that the buffer fills up. This ensures that SQL Server’s performance is not negatively impacted by a sudden flood of events that need to be written to the target faster than SQL Server can do so. As always, rule #1 of monitoring is that the monitoring system should impact the underlying system as little as possible! The latency setting similarly provides some buffer time for events to make their way to the target from memory. Typically, some latency is tolerable, and it ensures that events are not being written from memory to the target constantly. A latency of 0 or INFINITE means that events will only be written to the target when the buffer is full or the event session is closed. Lastly, the startup state determines if the event session is started immediately upon creation, or if it is created in a stopped state. There are a number of other settings that can be adjusted, such as whether to track causality, how to configure the memory partition mode, or set the event size cap. The Extended Events session creation documentation has good coverage of each of these options in greater detail than is in scope for this article: CREATE EVENT SESSION (Transact-SQL) – SQL Server | Microsoft Docs

Start the Extended Events session

Assuming STARTUP_STATE = OFF when the event session is created, the session will need to be started. This can be done with the following T-SQL: The event session may be stopped at any time with a similar script:

Read data from the Extended Events session file

The work thus far relies on the ability to quickly read data from an Extended Events session. This is by far the most challenging part of using Extended Events. The target data is written as XML and effectively making use of it requires XML parsing. SQL Server provides a function that can be used to read directly from this XML data called sys.fn_xe_file_target_read_file. This is how a query using this function will look: The query returns nothing, though: Image showing no data returned from query That was disappointing! The file name used for the file target will have additional numbers appended to it to allow it to differentiate files used for the event session. This is necessary if rollover files are used as multiple files cannot share the same name. Details about the file can be returned with a query against sys.server_event_session_fields: This query returns any file parameters for the event session target and can be filtered by joining back to sys.server_event_sessions: Image showing file parameters for extended event target file Note that the file name provided here does not match the physical file name but the name provided when the event session was created. The physical file looks like this: Image showing how to get file name of Extended Events data collection The function fn_xe_file_target_read_file can include wild cards, so there is no need to fish around for the exact physical file name: The results are as follows: Image depicting extended events data collection Retrieved are the timestamp and a big glob of XML. It is generally faster to store this data as XML and parse it asynchronously at a later time or as part of a structured process. XML shredding takes time and potentially hefty computing resources, so waiting on it is not ideal. It is important to note that for exceptionally high event volume, the fastest way to process the XML is to use PowerShell or a .NET application to directly read the XEL file. Doing that here is well out of scope for this article but is an exercise undertaken by others in the community and worth investigating if all else fails.

Extended Events data collection

It is here that this work moves from building blocks into building. This article has provided an overview of creating, configuring, and pulling data from Extended Events sessions. To go further, permanent database objects will need to be created to manage and store the XML and the event data derived from it. Extended Events are highly customizable and versatile. Therefore, efforts to build a process that uses the content discussed thus far will benefit from flexibility. There are many, many ways to solve this challenge, and each database environment will benefit from slightly different approaches. There is a great benefit in using T-SQL exclusively to implement and manage Extended Events. While the SQL Server Management Studio UI is convenient, it is not a scalable approach for large numbers of database servers. Therefore, I encourage the reader to pursue using T-SQL, regardless of the simplicity or complexity of event collection needs. The next article will combine the concepts presented here into a more permanent solution that can be tailored to a wide variety of SQL Server monitoring and data collection challenges. If you liked this article, you might also like Collecting SQL Server performance monitor data – Simple Talk (red-gate.com).

FAQs

1. What are SQL Server Extended Events?

SQL Server Extended Events (XEvents) is a lightweight, configurable event-capture framework introduced in SQL Server 2008 that replaced SQL Trace and SQL Server Profiler for diagnostic monitoring. Extended Events sessions capture specific events (query execution, deadlocks, wait types, memory allocations, errors) and route them to targets (in-memory ring buffer, on-disk event_file, histogram, pair_matching, event_counter) for analysis. The framework is designed to be low-overhead by default, extensible (new events are added in each SQL Server release), and scriptable (entire sessions can be created, managed, and queried via T-SQL). Extended Events is the only monitoring framework Microsoft actively develops – SQL Profiler remains functional but is deprecated for most use cases.

2. How do I create a SQL Server Extended Events session?

Use CREATE EVENT SESSION with clauses defining the events, actions, predicates, targets, and options. Minimal syntax: CREATE EVENT SESSION [MySession] ON SERVER ADD EVENT sqlserver.sql_statement_completed ADD TARGET package0.event_file (SET filename = ‘C:\XE\MySession.xel’) WITH (STARTUP_STATE = OFF). Then ALTER EVENT SESSION [MySession] ON SERVER STATE = START to begin capture. Add ACTION clauses inside ADD EVENT to capture additional context per event (session_id, client_app_name, sql_text, etc.). Add WHERE clauses inside ADD EVENT to filter what’s captured. Multiple targets can be added to a single session when you need different views of the same data (for example, a ring_buffer for live inspection plus an event_file for persistent history).

3. How do I read data from an Extended Events event_file target?

Query sys.fn_xe_file_target_read_file passing the file path pattern – this returns the raw XML event_data. Shred the XML with nodes() and value() to extract specific fields: SELECT event_data.value(‘(event/@name)[1]’, ‘nvarchar(100)’) AS event_name, event_data.value(‘(event/@timestamp)[1]’, ‘datetime2’) AS timestamp, event_data.value(‘(event/data[@name=”duration”]/value)[1]’, ‘bigint’) AS duration_microseconds FROM (SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file(‘C:\XE\MySession*.xel’, NULL, NULL, NULL)) AS e. For large files this can be slow – the XML shred is the typical bottleneck in Extended Events analysis. For routine reporting, stage the shredded events into a table once and query the table.

4. Are SQL Server Extended Events replacing SQL Profiler?

Yes – Extended Events has replaced SQL Profiler as the recommended monitoring framework since SQL Server 2012. SQL Profiler and SQL Trace remain available for backwards compatibility but are deprecated and receive no new functionality. Extended Events offers equivalent or broader event coverage with substantially lower overhead, more flexible targets (including histograms and pair-matching for specialised analysis), and better integration with Azure SQL Database (where Profiler doesn’t work at all). Most tasks that used Profiler – capturing long queries, finding deadlocks, tracing a specific login’s activity – have a direct Extended Events equivalent. If you’re currently using Profiler for new monitoring work, migrate to Extended Events; existing Profiler traces can run until you’re ready.

This document contains proprietary information and is protected by copyright law.

Copyright © 2026 Red Gate Software Limited. All rights reserved

Article tags

Recommended

About the author

Edward Pollack

See Profile

Ed Pollack has 20+ years of experience in database and systems administration, which has developed his passion for performance optimization, database design, and making things go faster. He has spoken at many SQLSaturdays, 24 Hours of PASS, and PASS Summit. This led him to organize SQLSaturday Albany, which has become an annual event for New York’s Capital Region. In his free time, Ed enjoys video games, traveling, cooking exceptionally spicy foods, and hanging out with his amazing wife and sons.

Edward's contributions

Edward's latest contributions: